﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Transactions;

using BusinessLogicLayer;
using BusinessLogicLayer.MetaDataList;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.ImportDataSetTableAdapters;

using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListCustomerNameDTSTableAdapters;

using Global.Class.Library;
using BrightIdeasSoftware;

namespace PresentationLayer
{
    public partial class DialogImport09 : Form, INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null) PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }


        private enum ETableFieldSpName
        {
            ETableRoot = 0,
            ETableName = 1,
            EFieldID = 2,
            ESPLoading = 3,
            EFieldGroup01ID = 4,
            EFieldGroup01Name = 5,
            EFieldGroup02ID = 6,
            EFieldGroup02NameA = 7,
            EFieldGroup02NameB = 8,
            EFieldRoot = 9
        }


        private OleDbDatabase.MappingTaskID mappingTaskID;
        private string lFileName;

        private GlobalEnum.EIColumnID lColumnID;
        private GlobalEnum.EIColumnID lColumnFilterID;
        private bool lInMarket;


        private string lColumnFilterFull = "";
        private string lColumnFilterRequired = "";



        private bool lhideUnHideAvailable = false;

        public bool lHideUnHideAvailable
        {
            get { return this.lhideUnHideAvailable; }
            set
            {
                if (this.lhideUnHideAvailable != value)
                {
                    this.lhideUnHideAvailable = value;

                    this.NotifyPropertyChanged("lHideUnHideAvailable");

                    this.lsLoadingAvailable();
                    this.lShowMapping();
                }
            }
        }

        private bool lhideUnHideRequired = true;

        public bool lHideUnHideRequired
        {
            get { return this.lhideUnHideRequired; }
            set
            {
                if (this.lhideUnHideRequired != value)
                {
                    this.lhideUnHideRequired = value;

                    this.NotifyPropertyChanged("lHideUnHideRequired");

                    this.lsLoadingRequired();
                    this.lShowMapping();
                }
            }
        }


        DataTable listDataAvailableDataTable;
        DataTable listDataRequiredDataTable;

        BindingSource bindingSourceDataAvailable;


        private int addressAreaID = -1;

        public int AddressAreaID
        {
            get { return this.addressAreaID; }
            set
            {
                if (this.addressAreaID != value)
                {
                    this.addressAreaID = value;

                    this.NotifyPropertyChanged("ColumnFilterRequired");

                    this.lsLoadingRequired();
                    this.lShowMapping();
                }
            }
        }


        private int customerTypeID = -1;

        public int CustomerTypeID
        {
            get { return this.customerTypeID; }
            set
            {
                if (this.customerTypeID != value)
                {
                    this.customerTypeID = value;

                    this.lsLoadingRequired();
                    this.lShowMapping();
                }
                this.NotifyPropertyChanged("CustomerTypeID");
            }
        }


        private string descriptionOfficial = "";

        public string DescriptionOfficial
        {
            get { return this.descriptionOfficial; }
            set
            {
                if (this.descriptionOfficial != value)
                {
                    this.descriptionOfficial = value;

                    this.NotifyPropertyChanged("DescriptionOfficial");

                    this.lsLoadingRequired();
                    this.lShowMapping();
                }
            }
        }

        private string columnFilterRequired = "";

        public string ColumnFilterRequired
        {
            get { return this.columnFilterRequired; }
            set
            {
                if (this.columnFilterRequired != value)
                {
                    this.columnFilterRequired = value;

                    this.NotifyPropertyChanged("ColumnFilterRequired");

                    if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)
                    {
                        this.lsLoadingAvailable();
                        if (this.comboBoxColumnFilter.SelectedItem != null && this.columnFilterRequired != "" && this.columnFilterRequired != "0")
                        {
                            int addressAreaID;
                            DataRow selectedDataRow = ((DataRowView)this.comboBoxColumnFilter.SelectedItem).Row;
                            if (selectedDataRow != null && int.TryParse(this.lfGetColumnValueMapFieldID(GlobalEnum.EIColumnID.EIAddressAreaID, (string)selectedDataRow["ColumnFilterValueOriginal"]), out addressAreaID))
                                if (this.AddressAreaID != addressAreaID && addressAreaID > 0)//DIEU KIEN addressAreaID > 0 NHAM MUC DICH: => KHONG LOAD [All Territory]: TUC this.AddressAreaID = 0: NHAM KHONG LAM CHAM QUA TRINH NAY
                                    this.AddressAreaID = addressAreaID;
                                else
                                    this.lShowMapping();
                            else
                                this.lShowMapping();
                        }
                        else
                            this.lShowMapping();
                    }
                }
            }
        }


        public DialogImport09(OleDbDatabase.MappingTaskID mappingTaskID, string excelFile, GlobalEnum.EIColumnID columnID, GlobalEnum.EIColumnID columnFilterID)
        {
            InitializeComponent();

            this.mappingTaskID = mappingTaskID;
            this.lFileName = excelFile;

            this.lColumnID = columnID;
            this.lColumnFilterID = columnFilterID;


            if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();


            this.buttonMapAll.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID ? true : false;
            this.toolStripButtonBack.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID ? false : true;
            this.toolStripButtonNext.Text = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID ? "Import" : "Next";

            this.checkBoxHideUnHideAvailable.DataBindings.Add("Checked", this, "lHideUnHideAvailable", true, DataSourceUpdateMode.OnPropertyChanged);
            this.checkBoxHideUnHideRequired.DataBindings.Add("Checked", this, "lHideUnHideRequired", true, DataSourceUpdateMode.OnPropertyChanged);

            this.comboBoxColumnFilter.Visible = this.lColumnFilterID != 0;
            this.comboBoxAddressAreaID.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID;
            this.comboBoxCustomerTypeID.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID;
            this.textBoxDescriptionOfficial.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID;

            this.dataGridColumnAvailable.Size = new Size(this.dataGridColumnAvailable.Size.Width, this.lColumnFilterID == 0 ? this.dataGridColumnAvailable.Size.Height : 551);
            this.dataTreeListViewMaster.Size = new Size(this.dataTreeListViewMaster.Size.Width, this.lColumnFilterID == 0 ? this.dataTreeListViewMaster.Size.Height : 512);

            this.dataGridColumnAvailable.Location = new Point(this.dataGridColumnAvailable.Location.X, this.lColumnFilterID == 0 ? this.dataGridColumnAvailable.Location.Y : this.comboBoxColumnFilter.Location.Y + this.comboBoxColumnFilter.Size.Height - 1);
            this.dataTreeListViewMaster.Location = new Point(this.dataTreeListViewMaster.Location.X, this.lColumnFilterID == 0 ? this.dataGridColumnAvailable.Location.Y : this.textBoxDescriptionOfficial.Location.Y + this.textBoxDescriptionOfficial.Size.Height - 1);

            StackedHeaderDecorator stackedHeaderDecoratorAvailable = new StackedHeaderDecorator(this.dataGridColumnAvailable);


            Global.Class.Library.GlobalVariables.ImportNextStep = 0;

        }

        private void DialogImport09_Load(object sender, EventArgs e)
        {
            try
            {
                if (this.lColumnFilterID != 0 && lColumnFilterRequired == "") //FILTER BY SELECTED COLUMN
                {
                    //INIT cbColumnFilter AND lColumnFilterFull AND lColumnFilterRequired USING IN TRANSACT SQL (INIT WHEN FORM LOAD ONLY). Me.cbColumnFilter: COLUMN 0: STRING CUA CAC REQUIREDCOLUMN CACH NHAU BOI: "~~--~~". COLUMN 1: DISPLAY. COLUMN 2: GIONG NHU COLUMN 0 BUT WITHOUT "~~--~~"!!! COLUMN 2: HIEN NAY DUOC DUNG BOI FUNCTION: lfGetColumnValueMapFieldID IN SUB lsLoadingRequired

                    DataRow dataRow;
                    DataTable columnFilterDataTable;
                    columnFilterDataTable = new DataTable("ListColumnFilter");
                    columnFilterDataTable.Columns.Add("ColumnFilterRequired", System.Type.GetType("System.String"));
                    columnFilterDataTable.Columns.Add("ColumnFilterDisplay", System.Type.GetType("System.String"));
                    columnFilterDataTable.Columns.Add("ColumnFilterValueOriginal", System.Type.GetType("System.String"));

                    dataRow = columnFilterDataTable.NewRow();

                    dataRow["ColumnFilterRequired"] = "@@!$#@!!@@";
                    dataRow["ColumnFilterDisplay"] = "[ALL data]";
                    dataRow["ColumnFilterValueOriginal"] = "0";
                    columnFilterDataTable.Rows.Add(dataRow);






                    //int j = 0;
                    string lColumnFilterDisplay = "";
                    string lColumnFilterValue = "";
                    string lColumnFilterValueOriginal = "";
                    //INIT cbColumnFilter AND lColumnFilterFull AND lColumnFilterRequired USING IN TRANSACT SQL (INIT WHEN FORM LOAD ONLY). Me.cbColumnFilter: COLUMN 0: STRING CUA CAC REQUIREDCOLUMN CACH NHAU BOI: "~~--~~". COLUMN 1: DISPLAY. COLUMN 2: GIONG NHU COLUMN 0 BUT WITHOUT "~~--~~"!!! COLUMN 2: HIEN NAY DUOC DUNG BOI FUNCTION: lfGetColumnValueMapFieldID IN SUB lsLoadingRequired
                    DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnID, ListDistributorColumn.ColumnName, ListColumnName.ColumnRequired FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " AND ListColumnName.ColumnGroupID = " + (int)this.lColumnFilterID + " ORDER BY ListColumnName.ColumnSortID DESC");
                    if (lRec.Rows.Count > 0)
                    {
                        foreach (DataRow lRecRow in lRec.Rows)
                        {
                            lColumnFilterFull = lColumnFilterFull + (lColumnFilterFull != "" ? ", " : "") + "[" + lRecRow["ColumnName"] + "]";
                            if ((bool)lRecRow["ColumnRequired"]) lColumnFilterRequired = lColumnFilterRequired + (lColumnFilterRequired != "" ? " + " : "") + "'~~--~~' + TRIM([" + lRecRow["ColumnName"] + "]) + '@@&&'";
                        }

                        lRec = OleDbDatabase.OpenExcelSheet(this.lFileName, OleDbDatabase.GetMappingSheetName(this.mappingTaskID), " DISTINCT " + lColumnFilterFull, "", lColumnFilterFull);
                        foreach (DataRow lRecRow in lRec.Rows)
                        {
                            lColumnFilterDisplay = ""; lColumnFilterValue = ""; lColumnFilterValueOriginal = "";
                            for (int j = 0; j <= lRec.Columns.Count - 1; j++) //LAY 1 FIELD (DE GHEP LAI VOI NHAU THANH MOT CHUOI)
                            {
                                if (lRecRow[j] != DBNull.Value)
                                {
                                    lColumnFilterDisplay = lColumnFilterDisplay + (lColumnFilterDisplay != "" ? "\t" : "") + "[" + (string)lRecRow[j] + "]";
                                    if (lColumnFilterRequired.IndexOf(lRec.Columns[j].ColumnName) >= 0)
                                    {//NOI STRING CUA TAT CAC CAC 'REQUIRED COLUMN' LAI VOI NHAU. NOTES: REQUIRED COLUMN ONLY
                                        lColumnFilterValue = lColumnFilterValue + "~~--~~" + ((string)lRecRow[j]).Trim() + "@@&&";
                                        lColumnFilterValueOriginal = lColumnFilterValueOriginal + (string)lRecRow[j];
                                    }
                                }
                            }
                            if (lColumnFilterValue != "")
                            {
                                dataRow = columnFilterDataTable.NewRow();

                                dataRow["ColumnFilterRequired"] = lColumnFilterValue;
                                dataRow["ColumnFilterDisplay"] = lColumnFilterDisplay;
                                dataRow["ColumnFilterValueOriginal"] = lColumnFilterValueOriginal;
                                columnFilterDataTable.Rows.Add(dataRow);
                            }
                        }
                    }


                    this.comboBoxColumnFilter.DataSource = columnFilterDataTable;
                    this.comboBoxColumnFilter.DisplayMember = "ColumnFilterDisplay";
                    this.comboBoxColumnFilter.ValueMember = "ColumnFilterRequired";
                    this.comboBoxColumnFilter.DataBindings.Add("SelectedValue", this, "ColumnFilterRequired", true, DataSourceUpdateMode.OnPropertyChanged);



                    this.comboBoxColumnFilter.Visible = true;


                    if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)
                    {
                        CommonMetaList commonMetaList = new CommonMetaList();

                        ERmgrUIP.ListAddressAreaDataTable listItemAddressAreaDataTable = commonMetaList.GetAddressArea();
                        this.comboBoxAddressAreaID.DataSource = listItemAddressAreaDataTable;
                        this.comboBoxAddressAreaID.DisplayMember = listItemAddressAreaDataTable.DescriptionColumn.ColumnName;
                        this.comboBoxAddressAreaID.ValueMember = listItemAddressAreaDataTable.AddressAreaIDColumn.ColumnName;
                        this.comboBoxAddressAreaID.DataBindings.Add("SelectedValue", this, "AddressAreaID", true, DataSourceUpdateMode.OnPropertyChanged);

                        ERmgrUIP.ListCustomerTypeDataTable listItemCustomerTypeDataTable = commonMetaList.GetCustomerType(true, "[All Channel]");
                        this.comboBoxCustomerTypeID.DataSource = listItemCustomerTypeDataTable;
                        this.comboBoxCustomerTypeID.DisplayMember = listItemCustomerTypeDataTable.DescriptionColumn.ColumnName;
                        this.comboBoxCustomerTypeID.ValueMember = listItemCustomerTypeDataTable.CustomerTypeIDColumn.ColumnName;
                        this.comboBoxCustomerTypeID.DataBindings.Add("SelectedValue", this, "CustomerTypeID", true, DataSourceUpdateMode.OnPropertyChanged);

                        this.textBoxDescriptionOfficial.DataBindings.Add("Text", this, "DescriptionOfficial", true);

                        this.CustomerTypeID = -1; //"[All Channel]"
                    }

                    this.ColumnFilterRequired = (string)columnFilterDataTable.Rows[columnFilterDataTable.Rows.Count >= 2 ? 1 : 0]["ColumnFilterRequired"];
                }

                else
                {
                    this.lsLoadingAvailable();
                    this.lsLoadingRequired();

                    this.lShowMapping();
                }
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        private void lsLoadingAvailable()
        {

            try
            {
                string SQL = "";
                string SQW = "";
                string lTempColAvAvailableRequired = "";
                string lTempColAvAvailable = "";
                string lFormatString = "";

                this.listDataAvailableDataTable = new DataTable("ListColumnAvailable");

                if (this.lColumnFilterID != 0 && (this.comboBoxColumnFilter == null || this.ColumnFilterRequired == "" || this.lColumnFilterRequired == "")) throw new Exception("");//KIEM TRA XEM CO Y/C FILER BY EXCELL RAW DATA HAY KHONG (TO vsFAvailable)


                DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnID, ListDistributorColumn.ColumnName, ListColumnName.ColumnRequired FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " AND ListColumnName.ColumnGroupID = " + (int)this.lColumnID + " ORDER BY ListColumnName.ColumnSortID DESC");
                if (lRec.Rows.Count > 0)
                {
                    this.listDataAvailableDataTable.Columns.Add("SerialID", System.Type.GetType("System.Int32"));
                    this.listDataAvailableDataTable.Columns.Add("ColumnValue", System.Type.GetType("System.String"));


                    foreach (DataRow lRecrow in lRec.Rows)
                    {
                        SQL = SQL + (SQL == "" ? "" : ",") + "[" + lRecrow["ColumnName"] + "]";
                        if ((bool)lRecrow["ColumnRequired"]) lTempColAvAvailableRequired = lTempColAvAvailableRequired + "~~--~~[" + lRecrow["ColumnName"] + "]@@&&";

                        if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID && (int)lRecrow["ColumnID"] == 760) SQW = "[" + lRecrow["ColumnName"] + "]" + " <> '' "; //PRE MAP ONLY

                        lFormatString = lFormatString + "|<" + this.lFileName.Substring(0, 10);

                        this.listDataAvailableDataTable.Columns.Add((string)lRecrow["ColumnName"], System.Type.GetType("System.String"));
                    }


                    this.listDataAvailableDataTable.Columns.Add("ColumnMapping", System.Type.GetType("System.String"));


                    lRec = OleDbDatabase.OpenExcelSheet(this.lFileName, OleDbDatabase.GetMappingSheetName(this.mappingTaskID), "DISTINCT " + SQL, SQW + (SQW == "" ? "" : " AND ") + (this.ColumnFilterRequired == "@@!$#@!!@@" || this.lColumnFilterRequired == "" ? "" : this.lColumnFilterRequired + " = '" + this.ColumnFilterRequired + "'"), SQL);

                    DataTable lRMp = new DataTable(); int i = 0;
                    if (!lHideUnHideAvailable) lRMp = SQLDatabase.GetDataTable("SELECT DISTINCT ColumnValue FROM " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = " + GlobalVariables.DistributorID); //INIT RECORDSET TO CHECK MAPPED ITEMS



                    foreach (DataRow lRecrow in lRec.Rows)
                    {
                        lTempColAvAvailable = "";
                        DataRow dataRow = this.listDataAvailableDataTable.NewRow();

                        for (int j = 0; j <= lRec.Columns.Count - 1; j++)
                        {
                            if (lRecrow[j] != DBNull.Value)
                            {
                                dataRow[lRec.Columns[j].ColumnName] = lRecrow[j];
                                if (lTempColAvAvailableRequired.IndexOf(lRec.Columns[j].ColumnName) > 0) lTempColAvAvailable = lTempColAvAvailable + lRecrow[j];  //NOI STRING CUA TAT CAC CAC 'REQUIRED COLUMN' LAI VOI NHAU. NOTES: REQUIRED COLUMN ONLY
                            }
                        }

                        if (lTempColAvAvailable != "" && (lHideUnHideAvailable || !lfCheckMappedAlready(lRMp, "", lTempColAvAvailable)))
                        {
                            dataRow["SerialID"] = ++i;
                            dataRow["ColumnValue"] = lTempColAvAvailable;
                            this.listDataAvailableDataTable.Rows.Add(dataRow);
                        }
                    }


                }

                bindingSourceDataAvailable = new BindingSource();
                bindingSourceDataAvailable.DataSource = this.listDataAvailableDataTable;

                this.dataGridColumnAvailable.DataSource = bindingSourceDataAvailable;
                this.dataGridColumnAvailable.Columns["SerialID"].Visible = false;

                this.buttonMapWithPreUPICode.Visible = this.lColumnID == GlobalEnum.EIColumnID.EICustomerID && SQW != "" ? true : false;
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }

        }



        private void lsLoadingRequired()
        {
            try
            {
                if (this.lColumnFilterID != 0 && (this.comboBoxColumnFilter == null || this.ColumnFilterRequired == "" || this.lColumnFilterRequired == "")) throw new Exception("");//KIEM TRA XEM CO Y/C FILER BY EXCELL RAW DATA HAY KHONG (TO vsFRequired)

                //SqlParameter[] sqlParameter = new SqlParameter[1];
                //sqlParameter[0] = new SqlParameter("lEmployeeID", GlobalVariables.GlobalUserInformation.UserID); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;

                SqlParameter[] sqlParameter = new SqlParameter[0];

                if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)
                {
                    Array.Resize<SqlParameter>(ref sqlParameter, sqlParameter.Length + 4);
                    sqlParameter[sqlParameter.Length - 4] = new SqlParameter("@AddressAreaID", 0); sqlParameter[sqlParameter.Length - 4].Value = this.AddressAreaID; sqlParameter[sqlParameter.Length - 4].SqlDbType = SqlDbType.Int; sqlParameter[sqlParameter.Length - 4].Direction = ParameterDirection.Input;
                    sqlParameter[sqlParameter.Length - 3] = new SqlParameter("@SalesmenID", 0); sqlParameter[sqlParameter.Length - 3].Value = -1; sqlParameter[sqlParameter.Length - 3].SqlDbType = SqlDbType.Int; sqlParameter[sqlParameter.Length - 3].Direction = ParameterDirection.Input;
                    sqlParameter[sqlParameter.Length - 2] = new SqlParameter("@CustomerTypeID", 0); sqlParameter[sqlParameter.Length - 2].Value = this.CustomerTypeID; sqlParameter[sqlParameter.Length - 2].SqlDbType = SqlDbType.Int; sqlParameter[sqlParameter.Length - 2].Direction = ParameterDirection.Input;
                    sqlParameter[sqlParameter.Length - 1] = new SqlParameter("@DescriptionOfficial", ""); sqlParameter[sqlParameter.Length - 1].Value = this.DescriptionOfficial; sqlParameter[sqlParameter.Length - 1].SqlDbType = SqlDbType.NVarChar; sqlParameter[sqlParameter.Length - 1].Direction = ParameterDirection.Input;

                    //sqlParameter[sqlParameter.Length-1] = new SqlParameter("lDescriptionOfficial", Me.cbDescriptionOfficial); sqlParameter[0].SqlDbType = SqlDbType.NVarChar; sqlParameter[0].Direction = ParameterDirection.Input;
                }

                if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID || this.lColumnID == GlobalEnum.EIColumnID.EICommodityID)
                {
                    //Array.Resize<SqlParameter>(ref sqlParameter, sqlParameter.Length + 1);
                    //sqlParameter[sqlParameter.Length - 1] = new SqlParameter("lViewableAll", 1); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;
                }

                ///DataTable lRec = SQLDatabase.GetDataTable(this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ESPLoading), CommandType.StoredProcedure, sqlParameter, (this.lColumnID == GlobalEnum.EIColumnID.EICustomerTypeID || this.lColumnID == GlobalEnum.EIColumnID.EICustomerChannelID || this.lColumnID == GlobalEnum.EIColumnID.EIAddressAreaID ? 1 : 0));
                DataTable lRec = SQLDatabase.GetDataTable(this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ESPLoading), CommandType.StoredProcedure, sqlParameter);

                DataTable lRMp = new DataTable();
                if (!this.lHideUnHideRequired) lRMp = SQLDatabase.GetDataTable("SELECT DISTINCT " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " FROM " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = " + GlobalVariables.DistributorID);//INIT RECORDSET TO CHECK MAPPED ITEMS

                if (lRec.Rows.Count > 0 && !this.lHideUnHideRequired)
                {
                    foreach (DataRow lRecrow in lRec.Rows)
                    {
                        if (lRecrow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)] != DBNull.Value && this.lfCheckMappedAlready(lRMp, this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID), lRecrow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)].ToString())) //THIS COMMAND LINE USED TO CHECK MAPPED ITEMS
                            lRec.Rows.Remove(lRecrow);
                    }
                }

                this.listDataRequiredDataTable = lRec.Copy();
                this.listDataRequiredDataTable.TableName = "ListCustomerTypeListingDataTable";


                do
                {
                    bool removeColumn = false;
                    foreach (DataColumn dataColumn in this.listDataRequiredDataTable.Columns)
                        if (dataColumn.ColumnName != this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) && dataColumn.ColumnName != "AncestorID" && dataColumn.ColumnName != "Description")
                        {
                            this.listDataRequiredDataTable.Columns.Remove(dataColumn);
                            removeColumn = true; break;
                        }

                    if (!removeColumn) break;
                } while (true);

                this.listDataRequiredDataTable.Columns.Add("ColumnMapping", System.Type.GetType("System.String"));



                DataSet bindingDataSet = new DataSet();
                bindingDataSet.Tables.Add(this.listDataRequiredDataTable);

                this.dataTreeListViewMaster.FullRowSelect = true;
                this.dataTreeListViewMaster.KeyAspectName = this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID);
                this.dataTreeListViewMaster.ParentKeyAspectName = "AncestorID";
                this.dataTreeListViewMaster.RootKeyValue = 0;

                this.dataTreeListViewMaster.DataMember = "ListCustomerTypeListingDataTable";
                this.dataTreeListViewMaster.DataSource = new DataViewManager(bindingDataSet);

                this.dataTreeListViewMaster.ExpandAll();


            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        private void lShowMapping()
        {
            try
            {
                this.listDataAvailableDataTable.Columns["ColumnMapping"].Expression = "";
                this.listDataRequiredDataTable.Columns["ColumnMapping"].Expression = "";

                DataTable lRec = SQLDatabase.GetDataTable(" SELECT * FROM " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = " + GlobalVariables.DistributorID);
                foreach (DataRow lRecrow in lRec.Rows)
                {
                    DataRow[] foundAvailableDataRows = this.listDataAvailableDataTable.Select("ColumnValue = '" + lRecrow["ColumnValue"] + "'");

                    if (foundAvailableDataRows.Length > 0)
                    {
                        DataRow[] foundRequiredDataRows = this.listDataRequiredDataTable.Select(this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " = " + lRecrow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)]);
                        if (foundRequiredDataRows.Length > 0)
                        {
                            foundAvailableDataRows[0]["ColumnMapping"] = foundRequiredDataRows[0]["Description"];
                            foundRequiredDataRows[0]["ColumnMapping"] = foundRequiredDataRows[0]["ColumnMapping"] + (foundRequiredDataRows[0]["ColumnMapping"] != DBNull.Value && foundRequiredDataRows[0]["ColumnMapping"] != "" ? "; " : "") + foundAvailableDataRows[0][this.listDataAvailableDataTable.Columns.Count - 2];//SHOW ALL IN CASE OF 1->n MAPPING
                        }
                    }
                    else
                    {
                        //DO NOTHING: SHOULD NOT REMOVE UNMATCH MAPPING (BOI VI KHONG PHAI FILE EXCELL NAO CUNG CO DAY DU 100% COMMODITYID HOAC CUSTOMERID
                    }
                }

                this.bindingSourceDataAvailable.ResetBindings(false);
                this.dataTreeListViewMaster.Refresh();
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        private void MapAndUnMapColumn(object sender, EventArgs e)
        {
            try
            {
                if (sender.Equals(this.buttonMapMe) || sender.Equals(this.buttonMapAll) || sender.Equals(this.buttonUnMapMe))
                {
                    if (this.dataGridColumnAvailable.CurrentRow == null) return;

                    DataRow listDataAvailableRow = ((DataRowView)this.dataGridColumnAvailable.CurrentRow.DataBoundItem).Row;
                    if (listDataAvailableRow == null || listDataAvailableRow["SerialID"] == DBNull.Value || listDataAvailableRow["ColumnValue"] == DBNull.Value) return; //Check for a valid selected row


                    if (sender.Equals(this.buttonMapMe) && this.dataTreeListViewMaster.SelectedIndex > 0)
                    {
                        ObjectListView objectListView = (ObjectListView)this.dataTreeListViewMaster;
                        DataRowView listDataRequiredRow = (DataRowView)objectListView.SelectedObject;

                        if (listDataRequiredRow != null && listDataRequiredRow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)] != DBNull.Value)//Check for a valid selected row
                        {
                            DataTable lRec = SQLDatabase.GetDataTable(" SELECT B." + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldRoot) + " AS Description FROM " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " A INNER JOIN " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableRoot) + " B ON A." + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " = B." + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " WHERE A.DistributorID = " + GlobalVariables.DistributorID + " AND A.ColumnValue = N'" + listDataAvailableRow["ColumnValue"] + "'");
                            if (lRec.Rows.Count > 0) listDataAvailableRow["ColumnMapping"] = lRec.Rows[0]["Description"]; else listDataAvailableRow["ColumnMapping"] = ""; //vsFAvailable: LOAD FROM SAVED DATABASE WETHER IT'S MAPPED

                            lRec = SQLDatabase.GetDataTable(" SELECT ColumnValue FROM " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = " + GlobalVariables.DistributorID + " AND " + this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " = " + listDataRequiredRow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)]);
                            if (lRec.Rows.Count > 0) listDataRequiredRow["ColumnMapping"] = lRec.Rows[0]["ColumnValue"]; else listDataRequiredRow["ColumnMapping"] = ""; //vsFRequired: LOAD FROM SAVED DATABASE WETHER IT'S MAPPED

                            if ((string)listDataAvailableRow["ColumnMapping"] != "" || (string)listDataRequiredRow["ColumnMapping"] != "")
                            {
                                if (MessageBox.Show(this, "This row was mapped already. Continue to map this row will overwrite the mapped information on the database." + "\r\n" + "\r\n" + "Press OK to continue, Press CANCEL to exit.", "Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Stop) == System.Windows.Forms.DialogResult.Cancel) return;//KIEM TRA NEU MAPPED ALREADY => DO NOTHING
                                if (MessageBox.Show(this, "Are you sure you want to map this row to another?" + "\r\n" + "Press 'YES' button to save, press 'NO' to quit?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == System.Windows.Forms.DialogResult.No) return;//KIEM TRA NEU MAPPED ALREADY: [AGAIN]
                            }

                            using (TransactionScope transactionScope = new TransactionScope())
                            {
                                if (!lfCustomerNameAddNew(true, (string)listDataAvailableRow["ColumnValue"], 0)) return; //T/H MAP CUSTOMER => ADD A NEW DEPENDENT CUSTOMER OBJECT

                                if (!lfMapMe((int)listDataRequiredRow[this.lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID)], (string)listDataAvailableRow["ColumnValue"])) throw new Exception("Error occur when exceute MapMe.");

                                transactionScope.Complete();
                            }

                        }
                    }


                    if (sender.Equals(this.buttonUnMapMe) && listDataAvailableRow["ColumnMapping"] != DBNull.Value)
                    {
                        DialogResult dialogResult = MessageBox.Show(this, "Are you sure you want to unmap this row?" + "\r\n" + "Press 'YES' button to unmap, press 'NO' to cancel?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Stop);
                        if (dialogResult == DialogResult.Yes)
                            using (TransactionScope transactionScope = new TransactionScope())
                            {
                                if (!lfUnMapMe((string)listDataAvailableRow["ColumnValue"])) throw new Exception("Error occur when exceute UnMapMe.");
                                transactionScope.Complete();
                            }
                    }


                    if (sender.Equals(this.buttonMapAll))//this.lColumnID = GlobalEnum.EIColumnID.EICustomerID ONLY
                    {
                        using (TransactionScope transactionScope = new TransactionScope())
                        {
                            if (!lfCustomerNameAddNew(false, (string)listDataAvailableRow["ColumnValue"], 0)) return;
                            transactionScope.Complete();
                        }

                        if (lHideUnHideRequired) this.lsLoadingRequired();
                        if (!lHideUnHideAvailable) this.lsLoadingAvailable();
                        if (!lHideUnHideAvailable || lHideUnHideRequired) this.lShowMapping();
                    }
                }


                if (sender.Equals(this.buttonMapWithPreUPICode) && this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)//this.lColumnID = GlobalEnum.EIColumnID.EICustomerID && PRE MAP ONLY
                {
                    int i = 0; int li = 0;
                    if (this.dataGridColumnAvailable.CurrentRow == null) return;

                    using (TransactionScope transactionScope = new TransactionScope())
                    {
                        foreach (DataRow listDataAvailableRow in listDataAvailableDataTable.Rows)
                            if (listDataAvailableRow["SerialID"] != DBNull.Value && listDataAvailableRow["ColumnValue"] != DBNull.Value) //Check for a valid selected row
                                if (listDataAvailableRow["UPICode"] != DBNull.Value && (string)listDataAvailableRow["UPICode"] != "") //UPICode LA PRE UPI CODE. XEM TABLE ListColumnName DE BIET CHI TIET
                                {
                                    i++;
                                    DataTable lRec = SQLDatabase.GetDataTable("SELECT CustomerID FROM ListCustomerName WHERE Description = N'" + listDataAvailableRow["UPICode"] + "' AND DependentOnly = 0 ");
                                    if (lRec.Rows.Count > 0)
                                        if (lfCustomerNameAddNew(true, (string)listDataAvailableRow["ColumnValue"], 0)) //ADD A NEW DEPENDENT CUSTOMER OBJECT
                                        { if (lfMapMe((int)lRec.Rows[0]["CustomerID"], (string)listDataAvailableRow["ColumnValue"])) li++; }//Me.vsFAvailable.Cell(flexcpForeColor, i, 1, i, lColAvAvailable) = cntColorInActive
                                        else
                                            return;
                                }

                        transactionScope.Complete();
                    }

                    if (i != li) MessageBox.Show("Warnning: " + (i - li).ToString("#.##0") + " customer(s) can not be mapped."); //Me.lbToolTipText.Visible = True: Me.lbToolTipText.ForeColor = vbRed: Me.lbToolTipText = IIf(li = i - 2, "", "Warnning: " & FormatNumber(i - 2 - li, 0) & " customer(s) can not be mapped.")
                }

                if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID) CommonStaticMethod.pfCustomerDeleteOrphan(); //DELETE CUSTOMER MO COI

                this.lShowMapping();

            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }


        private void toolStripButtonNext_Click(object sender, EventArgs e)
        {
            try
            {
                //THU TUC KIEM TRA XEM 100% CAC COLUMN DEU DA DUOC MAP: HIEN TAI THEO Y/C CUA UPI: BO DI KHONG KIEM TRA (DO DO KHI IMPORT CUSTOMER/ INVOICE SE GAP LOI DO KHONG MAP 100% DU LIEU)
                //     For i = 1 To Me.vsFAvailable.Rows - 1
                //        If Not IsEmpty(Me.vsFAvailable.RowData(i)) And Trim(Me.vsFAvailable.Cell(flexcpText, i, lColAvMapping)) = "" Then GoTo ERR_HANDLER
                //     Next i
                if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)// GlobalEnum.EIColumnID.EICustomerID LA MAPPING DIALOG CUOI CUNG, KHI DO SE LA STEP CUOI CUNG: IMPORT ALL EXCELL DATA TO InMarketMaster + InMarketDetail
                {
                    //If Me.cbColumnFilter.ListIndex <> 0 Then GoTo ERR_HANDLER

                    if (MessageBox.Show(this, "Are you sure you want to import this Excel file?" + "\r\n" + "\r\n" + "Important: Please check your mapping data carefully. If there is any error during this step, your data will be discard.", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) return;

                    int i = 0;
                    int lImportBatchID = 0;

                    TimeSpan timeout = TimeSpan.FromMinutes(45);
                    using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, timeout))
                    {
                        lImportBatchID = this.pfNewIDPrimary("ListImportBatchMaster", "ImportBatchID");//MANAGE BATCH

                        if (!lfCustomerSaveTracking(lImportBatchID)) return;

                        if (Global.Class.Library.GlobalVariables.ImportNextStep == 1)
                        {
                            Global.Class.Library.GlobalVariables.ImportNextStep = 0;

                            DataTable lExcell = OleDbDatabase.OpenExcelSheet(this.lFileName, OleDbDatabase.GetMappingSheetName(this.mappingTaskID), " *, " + lfGetColumnName(GlobalEnum.EIColumnID.EIInvoiceID, " & ") + " AS NMVN2006InvoiceID ", "", lfGetColumnName(GlobalEnum.EIColumnID.EIInvoiceID, ", "));

                            if (lExcell.Rows.Count > 0)
                            {
                                string lNMVN2006InvoiceID = "";
                                InMarketBLL lObj = new InMarketBLL();

                                DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnID, ListDistributorColumn.ColumnName, ListColumnName.ColumnGroupID, ListColumnName.ColumnRequired FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " ORDER BY ListColumnName.ColumnGroupID, ListColumnName.ColumnSortID");

                                foreach (DataRow lExcellRow in lExcell.Rows)
                                {
                                    if (lNMVN2006InvoiceID != lExcellRow["NMVN2006InvoiceID"].ToString()) //FINISH ONE INVOICE: ONE OBJECT IS FINISHED
                                    {
                                        if (lNMVN2006InvoiceID != "" && lObj != null)
                                            if (lObj.Save()) i++; else throw new Exception("Save error!"); //.Save(False)

                                        lNMVN2006InvoiceID = lExcellRow["NMVN2006InvoiceID"].ToString();

                                        lObj.New();//INIT NEW OBJECT

                                        lObj.InMarketMaster.InMarketDate = this.lfGetColumnValueToDateTime(lExcellRow, lRec, GlobalEnum.EIColumnID.EIInMarketDate);
                                        lObj.InMarketMaster.Reference = lNMVN2006InvoiceID;
                                        lObj.InMarketMaster.CustomerID = this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerID, true);
                                        lObj.InMarketMaster.CustomerValue = lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerID);
                                        lObj.InMarketMaster.SalesmenID = 1;// GetSomethingByID("ListCustomerName", "CustomerID", lObj.CustomerID, "SalesmenID", , , , , , , , , , , ClassConn);
                                        lObj.InMarketMaster.DistributorID = GlobalVariables.DistributorID;
                                        lObj.InMarketMaster.ImportBatchID = lImportBatchID;
                                        lObj.InMarketMaster.OrderOrReturnID = 1;

                                    }

                                    lObj.InMarketDetailList.Add(new InMarketDetail(0, this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EICommodityID, true), this.lfGetColumnValueToDouble(lExcellRow, lRec, GlobalEnum.EIColumnID.EIQuantity), this.lfGetColumnValueToDouble(lExcellRow, lRec, GlobalEnum.EIColumnID.EIUnitUPrice), this.lfGetColumnValueToDouble(lExcellRow, lRec, GlobalEnum.EIColumnID.EIUnitUPriceEX), 0, 0, 0, ""));
                                }

                                if (lObj.Save()) i++; else throw new Exception("Save error!"); //.Save(False) //SAVE LAST ROW (CONDISER THIS ISSUE)
                            }
                        }

                        SQLDatabase.ExecuteNonQuery("INSERT INTO ListImportBatchMaster(ImportBatchID, ImportFile, ImportDate, EmployeeID, InvoiceCount) VALUES (" + lImportBatchID + ", N'" + this.lFileName + "' , GetDate(), " + 1 + ", " + i + ")");//TAM THOI: this.UserOrganization.UserID = 1

                        transactionScope.Complete();
                    }


                }

                Global.Class.Library.GlobalVariables.ImportNextStep = 1;

                this.DialogResult = DialogResult.OK;
            }
            catch (Exception exception)
            {
                GlobalExceptionHandler.ShowExceptionMessageBox(this, exception);
            }
        }

        private void toolStripButtonEscapeOrBack_Click(object sender, EventArgs e)
        {
            if (sender.Equals(this.toolStripButtonBack)) Global.Class.Library.GlobalVariables.ImportNextStep = 2;
            this.DialogResult = DialogResult.Cancel;
        }


        #region Local Function

        private string lfGetTableFieldSpName(GlobalEnum.EIColumnID lColumnID, ETableFieldSpName lTableFieldSPName)
        {
            switch (lColumnID)
            {
                case GlobalEnum.EIColumnID.EICommodityID:
                    if (lTableFieldSPName == ETableFieldSpName.ETableRoot) return "ListItemCommodity";
                    if (lTableFieldSPName == ETableFieldSpName.ETableName) return "ListItemCommodityColumn";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldID) return "CommodityID";
                    if (lTableFieldSPName == ETableFieldSpName.ESPLoading) return "ListItemCommodityListing";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01ID) return "ItemCategoryID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01Name) return "CategoryName";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02ID) return "CommodityID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameA) return "Description";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameB) return "DescriptionOfficial";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldRoot) return "DescriptionOfficial";
                    return "";
                case GlobalEnum.EIColumnID.EIAddressAreaID:
                    if (lTableFieldSPName == ETableFieldSpName.ETableRoot) return "ListAddressArea";
                    if (lTableFieldSPName == ETableFieldSpName.ETableName) return "ListAddressAreaColumn";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldID) return "AddressAreaID";
                    if (lTableFieldSPName == ETableFieldSpName.ESPLoading) return "ListAddressAreaListing";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01ID) return "AddressAreaID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01Name) return "Description";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02ID) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameA) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameB) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldRoot) return "Description";
                    return "";
                case GlobalEnum.EIColumnID.EICustomerCategoryID:
                    return "";
                case GlobalEnum.EIColumnID.EICustomerTypeID:
                    if (lTableFieldSPName == ETableFieldSpName.ETableRoot) return "ListCustomerType";
                    if (lTableFieldSPName == ETableFieldSpName.ETableName) return "ListCustomerTypeColumn";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldID) return "CustomerTypeID";
                    if (lTableFieldSPName == ETableFieldSpName.ESPLoading) return "ListCustomerTypeListing";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01ID) return "CustomerTypeID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01Name) return "Description";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02ID) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameA) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameB) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldRoot) return "Description";
                    return "";
                case GlobalEnum.EIColumnID.EICustomerChannelID:
                    if (lTableFieldSPName == ETableFieldSpName.ETableRoot) return "ListCustomerChannel";
                    if (lTableFieldSPName == ETableFieldSpName.ETableName) return "ListCustomerChannelColumn";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldID) return "CustomerChannelID";
                    if (lTableFieldSPName == ETableFieldSpName.ESPLoading) return "ListCustomerChannelListing";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01ID) return "CustomerChannelID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01Name) return "Description";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02ID) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameA) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameB) return "";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldRoot) return "Description";
                    return "";
                case GlobalEnum.EIColumnID.EICustomerID:
                    if (lTableFieldSPName == ETableFieldSpName.ETableRoot) return "ListCustomerName";
                    if (lTableFieldSPName == ETableFieldSpName.ETableName) return "ListCustomerNameColumn";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldID) return "CustomerID";
                    if (lTableFieldSPName == ETableFieldSpName.ESPLoading) return "ListCustomerNameAddressAreaListing";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01ID) return "AddressAreaID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup01Name) return "AreaName";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02ID) return "CustomerID";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameA) return "Description";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldGroup02NameB) return "DescriptionOfficial";
                    if (lTableFieldSPName == ETableFieldSpName.EFieldRoot) return "DescriptionOfficial";
                    return "";
                default:
                    return "";
            }
        }


        private string lfGetColumnName(GlobalEnum.EIColumnID lColumnID, string lOperator)
        {
            string columnName = "";
            DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnName FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " AND ListColumnName.ColumnGroupID = " + (int)lColumnID + " AND ListColumnName.ColumnRequired = 1 ORDER BY ListColumnName.ColumnSortID ");
            foreach (DataRow lRecRow in lRec.Rows)
                columnName = "[" + lRecRow["ColumnName"] + "]" + (columnName != "" ? lOperator : "") + columnName;

            return columnName;
        }



        private int lfGetColumnValueToInt(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID)
        {
            return this.lfGetColumnValueToInt(lExcell, lRec, lColumnID, false);
        }
        private int lfGetColumnValueToInt(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID, bool lMapField)
        {
            string columnValue = this.lfGetColumnValue(lExcell, lRec, lColumnID, lMapField);

            int intValue = 0;
            if (int.TryParse(columnValue, out intValue))
                return intValue;
            else
                throw new Exception("Type mismatch. [" + lColumnID + ": " + columnValue + "]");
        }



        private double lfGetColumnValueToDouble(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID)
        {
            return this.lfGetColumnValueToDouble(lExcell, lRec, lColumnID, false);
        }

        private double lfGetColumnValueToDouble(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID, bool lMapField)
        {
            string columnValue = this.lfGetColumnValue(lExcell, lRec, lColumnID, lMapField);

            double intValue = 0;
            if (double.TryParse(columnValue, out intValue))
                return intValue;
            else
                throw new Exception("Type mismatch. [" + lColumnID + ": " + columnValue + "]");
        }



        private DateTime lfGetColumnValueToDateTime(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID)
        {
            return this.lfGetColumnValueToDateTime(lExcell, lRec, lColumnID, false);
        }

        private DateTime lfGetColumnValueToDateTime(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID, bool lMapField)
        {
            string columnValue = this.lfGetColumnValue(lExcell, lRec, lColumnID, lMapField);

            DateTime dateTimeValue; int intValue;

            if (DateTime.TryParse(columnValue, out dateTimeValue))
                return dateTimeValue;
            else
                if (columnValue.Length >= 8 && int.TryParse(columnValue, out intValue))
                    return new DateTime(int.Parse(columnValue.Substring(0, 4)), int.Parse(columnValue.Substring(4, 2)), int.Parse(columnValue.Substring(6, 2)));
                else
                    throw new Exception("Type mismatch. [" + lColumnID + ": " + columnValue + "]");
        }







        private string lfGetColumnValue(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID)
        {
            return this.lfGetColumnValue(lExcell, lRec, lColumnID, false);
        }

        private string lfGetColumnValue(DataRow lExcell, DataTable lRec, GlobalEnum.EIColumnID lColumnID, bool lMapField)
        {
            string columnValue = "";
            DataRow[] foundDataRows = lRec.Select("ColumnID = " + (int)lColumnID + " OR (ColumnGroupID = " + (int)lColumnID + " AND ColumnRequired = 1)");
            foreach (DataRow foundDataRow in foundDataRows)
            {
                for (int i = 0; i <= lExcell.Table.Columns.Count - 1; i++) //LAY 1 FIELD (DE GHEP LAI VOI NHAU THANH MOT CHUOI)
                    if (lExcell.Table.Columns[i].ColumnName == (string)foundDataRow["ColumnName"])
                    { columnValue = lExcell[i] + columnValue; break; }
            }// columnValue: VALUE OF REQUIRED FIELD ON Excel file


            if (lMapField && columnValue != "") //IF lMapField => GET MAPPING ID
                columnValue = this.lfGetColumnValueMapFieldID(lColumnID, columnValue);

            columnValue = columnValue.Trim(); columnValue = columnValue.ToUpper();
            return columnValue;
        }

        private string lfGetColumnValueMapFieldID(GlobalEnum.EIColumnID lColumnID, string lColumnValue)
        {
            string columnValueMapFieldID = "";
            DataTable lRCol = SQLDatabase.GetDataTable("SELECT TOP 1 " + this.lfGetTableFieldSpName(lColumnID, ETableFieldSpName.EFieldID) + " FROM " + this.lfGetTableFieldSpName(lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = " + GlobalVariables.DistributorID + " AND ColumnValue = N'" + lColumnValue + "'");
            if (lRCol.Rows.Count > 0) columnValueMapFieldID = lRCol.Rows[0][0].ToString(); else columnValueMapFieldID = "0";

            return columnValueMapFieldID;
        }


        private bool lfCheckMappedAlready(DataTable lRec, string lFieldID, string lColumnValue)
        {
            DataRow[] foundDataRows;
            if (lFieldID != "") //CHECK MAPPED ITEM OF REQUIRED LIST (UPI DATABASE - UPI PRIMARY KEY: DATATYPE = LONG)
                foundDataRows = lRec.Select(lFieldID + " = " + lColumnValue);//lFieldID: NAME OF PRIMARY ID
            else //CHECK MAPPED ITEM OF AVAILABLE LIST (EXCEL FILES - EXCELL PRIMARY KEY: DATATYPE = STRING [NOI CHUOI, TAO THANH COMPOUND STRING])
                foundDataRows = lRec.Select("ColumnValue = '" + lColumnValue + "'");

            return foundDataRows.Length > 0;
        }




        private bool lfMapMe(int lFieldID, string lColumnValue)
        {
            SqlParameter[] sqlParameter = new SqlParameter[5];
            sqlParameter[0] = new SqlParameter("DistributorID", GlobalVariables.DistributorID); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("SerialID", this.pfNewIDSenior(lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName), "DistributorID", "SerialID", GlobalVariables.DistributorID)); sqlParameter[1].SqlDbType = SqlDbType.Int; sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[2] = new SqlParameter("FieldID", lFieldID); sqlParameter[2].SqlDbType = SqlDbType.Int; sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[3] = new SqlParameter("ColumnValue", lColumnValue); sqlParameter[3].SqlDbType = SqlDbType.NVarChar; sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[4] = new SqlParameter("MultipleMap", (this.lColumnID == GlobalEnum.EIColumnID.EICommodityID || this.lColumnID == GlobalEnum.EIColumnID.EIAddressAreaID || this.lColumnID == GlobalEnum.EIColumnID.EICustomerTypeID || this.lColumnID == GlobalEnum.EIColumnID.EICustomerChannelID || this.lColumnID == GlobalEnum.EIColumnID.EICustomerID ? 1 : 0)); sqlParameter[4].SqlDbType = SqlDbType.Int; sqlParameter[4].Direction = ParameterDirection.Input;//CHI CO T/H EICommodityID OR EIAddressAreaID OR EICustomerTypeID OR EICustomerChannelID OR EICustomerID MOI DUOC AP DUNG: MAP n TO 1 (VE THIET KE: TAT CA CAC T/H KHAC VAN CO THE AP DUNG MA KHONG CAN SUA CODE. KHI CAN AP DUNG MAPPING 1-n: CHI CAN BO SUNG DUY NHAT O CHO NAY LA OK)


            int aclEditable = SQLDatabase.ExecuteNonQuery("Map" + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName), CommandType.StoredProcedure, sqlParameter);


            if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)
            {
                CommonStaticMethod.lSaveCustomerCodeUnMap(lColumnValue);
                CommonStaticMethod.lSaveCustomerCodeMap(lFieldID);
            }

            return aclEditable != 0;
        }

        private bool lfUnMapMe(string lColumnValue)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("DistributorID", GlobalVariables.DistributorID); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("ColumnValue", lColumnValue); sqlParameter[1].SqlDbType = SqlDbType.NVarChar; sqlParameter[1].Direction = ParameterDirection.Input;

            int aclEditable = SQLDatabase.ExecuteNonQuery("UnMap" + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName), CommandType.StoredProcedure, sqlParameter);

            if (this.lColumnID == GlobalEnum.EIColumnID.EICustomerID)
            {
                CommonStaticMethod.lSaveCustomerCodeUnMap(lColumnValue);
            }

            return aclEditable != 0;
        }

        private bool lfCustomerNameAddNew(bool lDependentOnly, string lColumnValue, int lSalesmenID)
        {
            try
            {
                if (this.lColumnID != GlobalEnum.EIColumnID.EICustomerID) return true; //DIEU KIEN NAY DAM BAO CHI THUC HIEN FUNCTION SAU ONLY KHI ADD OR MAP CUSTOMER. BOI VI: THU TUC NAY DUOC CALL BOI BUTTON: lcmdMapAll (TUC CUSTOMER.COPY AND MAP) VA BUTTON lcmdMapMe (THU TUC MAP DUNG CHUNG DUNG DE MAP MOT DOI TUONG BAT KY).

                if (lDependentOnly) //CHI ADD THONG TIN CHO MOT CUSTOMER TRONG QUAN HE 1-N MA THOI
                {
                    DataTable lRec = SQLDatabase.GetDataTable("SELECT TOP 1 CustomerID FROM ListCustomerName WHERE DescriptionOriginal = '" + lColumnValue + "'");
                    if (lRec.Rows.Count > 0) return true;
                }

                DataTable lExcell = OleDbDatabase.OpenExcelSheet(this.lFileName, OleDbDatabase.GetMappingSheetName(this.mappingTaskID), " * ", " '#' + " + lfGetColumnName(this.lColumnID, " + ") + " = '#" + lColumnValue + "'", "");//DUNG KY TU '#' & CUSTOMERID: DE IMPLICIT CONVERT DATA TYPE IF CUSTOMERID IS A NUMERIC FIELD

                if (lExcell.Rows.Count > 0)
                {
                    DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnID, ListDistributorColumn.ColumnName, ListColumnName.ColumnGroupID, ListColumnName.ColumnRequired FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " ORDER BY ListColumnName.ColumnGroupID, ListColumnName.ColumnSortID");

                    ListCustomerNameBLL lObjCustomer = new ListCustomerNameBLL();
                    lObjCustomer.New();
                    lObjCustomer.ListCustomerNameMaster.DescriptionOriginal = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerID);
                    lObjCustomer.ListCustomerNameMaster.DescriptionOfficial = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerName);
                    lObjCustomer.ListCustomerNameMaster.VATCode = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerVATCode);
                    lObjCustomer.ListCustomerNameMaster.ContactName = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerContactName);
                    lObjCustomer.ListCustomerNameMaster.AttentionName = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerAttentionName);
                    lObjCustomer.ListCustomerNameMaster.Telephone = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerTelephone);
                    lObjCustomer.ListCustomerNameMaster.EMailAddress = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerEMail);
                    lObjCustomer.ListCustomerNameMaster.AddressNo = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerAddressNo);
                    lObjCustomer.ListCustomerNameMaster.AddressStreet = lfGetColumnValue(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerStreet);

                    lObjCustomer.ListCustomerNameMaster.CustomerCategoryID = 1;
                    lObjCustomer.ListCustomerNameMaster.CustomerTypeID = this.lfGetColumnValueToInt(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerTypeID, true);
                    lObjCustomer.ListCustomerNameMaster.CustomerChannelID = this.lfGetColumnValueToInt(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EICustomerChannelID, true);
                    lObjCustomer.ListCustomerNameMaster.CustomerGroupID = 1;
                    lObjCustomer.ListCustomerNameMaster.AddressAreaID = this.lfGetColumnValueToInt(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EIAddressAreaID, true);
                    lObjCustomer.ListCustomerNameMaster.DistributorID = GlobalVariables.DistributorID;

                    lObjCustomer.ListCustomerNameMaster.EntryDate = this.lfGetColumnValueToDateTime(lExcell.Rows[0], lRec, GlobalEnum.EIColumnID.EIInMarketDate);


                    if (lSalesmenID == 0) //KHI ADD NEW: ALLOW USER SELECT lSalesmenID
                    {
                        DialogImport09SalesmenID dialogImport09SalesmenID = new DialogImport09SalesmenID(lObjCustomer.ListCustomerNameMaster.AddressAreaID);
                        if (dialogImport09SalesmenID.ShowDialog() == DialogResult.OK)
                        {
                            lObjCustomer.ListCustomerNameMaster.SalesmenID = dialogImport09SalesmenID.SalesmenID;
                            dialogImport09SalesmenID.Dispose();
                        }
                        else throw new Exception("");
                    } //WHEN MAP 1-N ONLY (TUC MAP SELECTED CUSTOMER TO AN EXISTING CUSTOMER): USE THE SAME lSalesmenID WITH THE EXISTING CUSTOMER

                    lObjCustomer.ListCustomerNameMaster.DependentOnly = lDependentOnly;

                    if (!lObjCustomer.Save()) //CHI MAP KHI KHONG PHAI LA lDependentOnly  ---------- Save(False)
                        throw new Exception("Save customer error!");
                    else
                        if (!lDependentOnly)
                            if (!lfMapMe(lObjCustomer.CustomerID, lColumnValue))
                                throw new Exception("Map customer after add error!");
                }

                return true;
            }
            catch (Exception exception)
            {
                throw exception;
            }

        }

        private bool lfCustomerSaveTracking(int lImportBatchID)
        {
            string lDistributorCustomer = ""; int lImportBatchIDMinor = 0;

            DialogImport09CustomerTracking dialogImport09CustomerTracking = new DialogImport09CustomerTracking();
            if (dialogImport09CustomerTracking.ShowDialog() == DialogResult.OK)
            {
                if (dialogImport09CustomerTracking.IsCustomerTracking)
                {
                    if (dialogImport09CustomerTracking.IsCustomerMakeActive) lImportBatchIDMinor = this.pfNewIDPrimaryMinor("ListCustomerNameTracking", "ImportBatchID"); //MANAGE MINOR BATCH

                    DataTable lExcell = OleDbDatabase.OpenExcelSheet(this.lFileName, OleDbDatabase.GetMappingSheetName(this.mappingTaskID), "  *, '#' & " + this.lfGetColumnName(GlobalEnum.EIColumnID.EICustomerID, " & ") + " AS DistributorCustomer ", "  '#' & " + this.lfGetColumnName(GlobalEnum.EIColumnID.EICustomerID, " & "), "");
                    if (lExcell.Rows.Count > 0)
                    {
                        DataTable lRec = SQLDatabase.GetDataTable("SELECT ListDistributorColumn.ColumnID, ListDistributorColumn.ColumnName, ListColumnName.ColumnGroupID, ListColumnName.ColumnRequired FROM ListDistributorColumn INNER JOIN ListColumnName ON ListDistributorColumn.ColumnID = ListColumnName.ColumnID WHERE ListDistributorColumn.DistributorID = " + GlobalVariables.DistributorID + " ORDER BY ListColumnName.ColumnGroupID, ListColumnName.ColumnSortID");

                        foreach (DataRow lExcellRow in lExcell.Rows)
                        {
                            if (lDistributorCustomer != (string)lExcellRow["DistributorCustomer"]) //CHECK AND SAVE TRACKING FOR EACH DistributorCustomer
                            {
                                if (!pfCustomerTracking(lImportBatchID, this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerID, true), GlobalVariables.DistributorID, this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerTypeID, true), this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerChannelID, true), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerName), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerVATCode), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerContactName), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerAttentionName), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerTelephone), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerEMail), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerAddressNo), lfGetColumnValue(lExcellRow, lRec, GlobalEnum.EIColumnID.EICustomerStreet), this.lfGetColumnValueToInt(lExcellRow, lRec, GlobalEnum.EIColumnID.EIAddressAreaID, true), dialogImport09CustomerTracking.IsCustomerMakeActive, lImportBatchIDMinor))
                                    throw new Exception("Cannot tracking customer");

                                lDistributorCustomer = (string)lExcellRow["DistributorCustomer"];
                            }
                        }

                        SQLDatabase.ExecuteNonQuery("UPDATE ListCustomerNameTracking SET FirstChangeLog = 0 ");
                        SQLDatabase.ExecuteNonQuery("UPDATE ListCustomerNameTracking SET FirstChangeLog = 1 WHERE CustomerID IN (SELECT CustomerID FROM ListCustomerNameTracking GROUP BY CustomerID HAVING COUNT(SerialID) = 1) ");
                    }

                    MessageBox.Show(this, "Finish save tracking. Click Ok to " + (dialogImport09CustomerTracking.IsQuitAfterCheck ? "exit." : "import invoice data."), "Warning", MessageBoxButtons.OK, dialogImport09CustomerTracking.IsQuitAfterCheck ? MessageBoxIcon.Information : MessageBoxIcon.Warning);
                }

                Global.Class.Library.GlobalVariables.ImportNextStep = dialogImport09CustomerTracking.IsCustomerTracking && dialogImport09CustomerTracking.IsQuitAfterCheck ? 0 : 1;

                dialogImport09CustomerTracking.Dispose();

                return true;
            }
            else return false;
        }

        private ListCustomerNameTableAdapter masterTableAdapter;
        protected ListCustomerNameTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListCustomerNameTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListCustomerNameTrackingTableAdapter trackingTableAdapter;
        protected ListCustomerNameTrackingTableAdapter TrackingTableAdapter
        {
            get
            {
                if (trackingTableAdapter == null) trackingTableAdapter = new ListCustomerNameTrackingTableAdapter();
                return trackingTableAdapter;
            }
        }


        private bool pfCustomerTracking(int lImportBatchID, int lCustomerID, int lDistributorID, int lCustomerTypeID, int lCustomerChannelID, string lDescriptionOfficial, string lVATCode, string lContactName, string lAttentionName, string lTelephone, string lEMailAddress, string lAddressNo, string lAddressStreet, int lAddressAreaID, bool lCustomerMakeActive, int lImportBatchIDMinor)
        {
            if (lCustomerMakeActive)
            {
                ListCustomerNameDTS.ListCustomerNameDataTable masterDataTable;
                ListCustomerNameDTS.ListCustomerNameRow masterRow;

                masterDataTable = this.MasterTableAdapter.GetData(lCustomerID);

                if (masterDataTable.Count > 0)
                {
                    masterRow = masterDataTable[0];

                    ImportDataSet.ListCustomerNameTrackingDataTable trackingDataTable;
                    ImportDataSet.ListCustomerNameTrackingRow trackingRow;

                    trackingDataTable = new ImportDataSet.ListCustomerNameTrackingDataTable();
                    trackingRow = trackingDataTable.NewListCustomerNameTrackingRow();//OPEN, BUT SAVE ONLY WHEN CHANGE OCCURED


                    if (lCustomerTypeID != 0 && masterRow.CustomerTypeID != lCustomerTypeID) { trackingRow.CustomerTypeID = masterRow.CustomerTypeID; masterRow.CustomerTypeID = lCustomerTypeID; }
                    if (lCustomerChannelID != 0 && masterRow.CustomerChannelID != lCustomerChannelID) { trackingRow.CustomerChannelID = masterRow.CustomerChannelID; masterRow.CustomerChannelID = lCustomerChannelID; }
                    if (lDescriptionOfficial != "" && masterRow.DescriptionOfficial != lDescriptionOfficial) { trackingRow.DescriptionOfficial = masterRow.DescriptionOfficial; masterRow.DescriptionOfficial = lDescriptionOfficial; }
                    if (lVATCode != "" && masterRow.VATCode != lVATCode) { trackingRow.VATCode = masterRow.VATCode; masterRow.VATCode = lVATCode; }
                    if (lContactName != "" && masterRow.ContactName != lContactName) { trackingRow.ContactName = masterRow.ContactName; masterRow.ContactName = lContactName; }
                    if (lAttentionName != "" && masterRow.AttentionName != lAttentionName) { trackingRow.AttentionName = masterRow.AttentionName; masterRow.AttentionName = lAttentionName; }
                    if (lTelephone != "" && masterRow.Telephone != lTelephone) { trackingRow.Telephone = masterRow.Telephone; masterRow.Telephone = lTelephone; }
                    if (lEMailAddress != "" && masterRow.EMailAddress != lEMailAddress) { trackingRow.EMailAddress = masterRow.EMailAddress; masterRow.EMailAddress = lEMailAddress; }
                    if (lAddressNo != "" && masterRow.AddressNo != lAddressNo) { trackingRow.AddressNo = masterRow.AddressNo; masterRow.AddressNo = lAddressNo; }
                    if (lAddressStreet != "" && masterRow.AddressStreet != lAddressStreet) { trackingRow.AddressStreet = masterRow.AddressStreet; masterRow.AddressStreet = lAddressStreet; }
                    if (lAddressAreaID != 0 && masterRow.AddressAreaID != lAddressAreaID) { trackingRow.AddressAreaID = masterRow.AddressAreaID; masterRow.AddressAreaID = lAddressAreaID; }

                    if (masterRow.RowState != DataRowState.Unchanged)
                    {
                        trackingRow.SerialID = this.pfNewIDPrimaryMinor("ListCustomerNameTracking", "SerialID");
                        trackingRow.ImportBatchID = lImportBatchIDMinor;
                        trackingRow.CustomerID = lCustomerID;
                        trackingRow.DistributorID = lDistributorID;


                        if (trackingRow.IsNull(trackingDataTable.CustomerTypeIDColumn)) trackingRow.CustomerTypeID = 0;
                        if (trackingRow.IsNull(trackingDataTable.CustomerChannelIDColumn)) trackingRow.CustomerChannelID = 0;
                        if (trackingRow.IsNull(trackingDataTable.DescriptionOfficialColumn)) trackingRow.DescriptionOfficial = "";
                        if (trackingRow.IsNull(trackingDataTable.VATCodeColumn)) trackingRow.VATCode = "";
                        if (trackingRow.IsNull(trackingDataTable.ContactNameColumn)) trackingRow.ContactName = "";
                        if (trackingRow.IsNull(trackingDataTable.AttentionNameColumn)) trackingRow.AttentionName = "";
                        if (trackingRow.IsNull(trackingDataTable.TelephoneColumn)) trackingRow.Telephone = "";
                        if (trackingRow.IsNull(trackingDataTable.EMailAddressColumn)) trackingRow.EMailAddress = "";
                        if (trackingRow.IsNull(trackingDataTable.AddressNoColumn)) trackingRow.AddressNo = "";
                        if (trackingRow.IsNull(trackingDataTable.AddressStreetColumn)) trackingRow.AddressStreet = "";
                        if (trackingRow.IsNull(trackingDataTable.AddressAreaIDColumn)) trackingRow.AddressAreaID = 0;


                        trackingRow.FirstChangeLog = false;

                        trackingDataTable.AddListCustomerNameTrackingRow(trackingRow);
                        if (this.TrackingTableAdapter.Update(trackingRow) != 1) throw new Exception("Error on saving tracking");
                        if (this.MasterTableAdapter.Update(masterRow) != 1) throw new Exception("Error on making inactive customer");
                    }
                }
            }

            DataTable dataTable;

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND CustomerTypeID =  " + lCustomerTypeID);
            if (dataTable.Rows.Count > 0) lCustomerTypeID = 0;

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND CustomerChannelID =  " + lCustomerChannelID);
            if (dataTable.Rows.Count > 0) lCustomerChannelID = 0;

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND DescriptionOfficial = N'" + lDescriptionOfficial + "'");
            if (dataTable.Rows.Count > 0) lDescriptionOfficial = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND VATCode = N'" + lVATCode + "'");
            if (dataTable.Rows.Count > 0) lVATCode = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND ContactName = N'" + lContactName + "'");
            if (dataTable.Rows.Count > 0) lContactName = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND AttentionName = N'" + lAttentionName + "'");
            if (dataTable.Rows.Count > 0) lAttentionName = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND Telephone = N'" + lTelephone + "'");
            if (dataTable.Rows.Count > 0) lTelephone = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND EMailAddress = N'" + lEMailAddress + "'");
            if (dataTable.Rows.Count > 0) lEMailAddress = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND AddressNo = N'" + lAddressNo + "'");
            if (dataTable.Rows.Count > 0) lAddressNo = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND AddressStreet = N'" + lAddressStreet + "'");
            if (dataTable.Rows.Count > 0) lAddressStreet = "";

            dataTable = SQLDatabase.GetDataTable("SELECT SerialID FROM ListCustomerNameTracking WHERE CustomerID = " + lCustomerID + " AND DistributorID = " + lDistributorID + " AND AddressAreaID = " + lAddressAreaID);
            if (dataTable.Rows.Count > 0) lAddressAreaID = 0;


            if (lCustomerTypeID != 0 || lCustomerChannelID != 0 || lDescriptionOfficial != "" || lVATCode != "" || lContactName != "" || lAttentionName != "" || lTelephone != "" || lEMailAddress != "" || lAddressNo != "" || lAddressStreet != "" || lAddressAreaID != 0)
            {
                ImportDataSet.ListCustomerNameTrackingDataTable trackingDataTable;
                ImportDataSet.ListCustomerNameTrackingRow trackingRow;

                trackingDataTable = new ImportDataSet.ListCustomerNameTrackingDataTable();
                trackingRow = trackingDataTable.NewListCustomerNameTrackingRow();

                trackingRow.SerialID = this.pfNewIDPrimary("ListCustomerNameTracking", "SerialID");
                trackingRow.ImportBatchID = lImportBatchID;
                trackingRow.CustomerID = lCustomerID;
                trackingRow.DistributorID = lDistributorID;
                trackingRow.CustomerTypeID = lCustomerTypeID;
                trackingRow.CustomerChannelID = lCustomerChannelID;
                trackingRow.DescriptionOfficial = lDescriptionOfficial;
                trackingRow.VATCode = lVATCode;
                trackingRow.ContactName = lContactName;
                trackingRow.AttentionName = lAttentionName;
                trackingRow.Telephone = lTelephone;
                trackingRow.EMailAddress = lEMailAddress;
                trackingRow.AddressNo = lAddressNo;
                trackingRow.AddressStreet = lAddressStreet;
                trackingRow.AddressAreaID = lAddressAreaID;
                trackingRow.FirstChangeLog = false;

                trackingDataTable.AddListCustomerNameTrackingRow(trackingRow);
                if (this.TrackingTableAdapter.Update(trackingRow) != 1) throw new Exception("Error on adding tracking");
            }

            return true;
        }



        #endregion





        #region Some general method

        private int pfNewIDPrimary(string tableName, string primaryFieldName)
        {
            int primaryID = 1;
            DataTable lRec = SQLDatabase.GetDataTable("SELECT Max(" + primaryFieldName + ") AS MaxID FROM " + tableName);
            if (lRec.Rows.Count > 0)
                if (lRec.Rows[0]["MaxID"] == DBNull.Value)
                    primaryID = 1;
                else
                    primaryID = (int)lRec.Rows[0]["MaxID"] + 1;

            return primaryID;
        }

        private int pfNewIDPrimaryMinor(string tableName, string primaryFieldName)
        {
            int primaryID = -1;
            DataTable lRec = SQLDatabase.GetDataTable("SELECT Min(" + primaryFieldName + ") AS MinID FROM " + tableName + " WHERE " + primaryFieldName + " < 0 ");
            if (lRec.Rows.Count > 0)
                if (lRec.Rows[0]["MinID"] == DBNull.Value)
                    primaryID = -1;
                else
                    primaryID = (int)lRec.Rows[0]["MinID"] - 1;


            return primaryID;
        }

        private int pfNewIDSenior(string tableName, string primaryFieldName, string seniorFieldName, int primaryValue)
        {
            try
            {
                int seniorID = 1;
                DataTable lRec = SQLDatabase.GetDataTable("SELECT Max(" + seniorFieldName + ") AS MaxID FROM " + tableName + " WHERE " + primaryFieldName + " = " + primaryValue);

                if (lRec.Rows.Count > 0)
                    if (lRec.Rows[0]["MaxID"] == DBNull.Value)
                        seniorID = 1;
                    else
                        seniorID = (int)lRec.Rows[0]["MaxID"] + 1;


                return seniorID;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        #endregion

        private void RestoreProcedure()
        {
            string queryString;


            queryString = "     @SerialID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      SerialID, ImportBatchID, CustomerID, DistributorID, CustomerTypeID, DescriptionOfficial, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, FirstChangeLog, CustomerChannelID " + "\r\n";
            queryString = queryString + "       FROM        ListCustomerNameTracking " + "\r\n";
            queryString = queryString + "       WHERE       SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameTrackingSelect", queryString);


            queryString = "     @SerialID int, @ImportBatchID int, @CustomerID int,	@DistributorID int,	@CustomerTypeID int, @DescriptionOfficial nvarchar(100), @VATCode nvarchar(60),	@ContactName nvarchar(100),	@AttentionName nvarchar(60), @Telephone nvarchar(60), @EMailAddress nvarchar(60), @AddressNo nvarchar(150), @AddressStreet nvarchar(150), @AddressAreaID int, @FirstChangeLog bit, @CustomerChannelID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListCustomerNameTracking (SerialID, ImportBatchID, CustomerID, DistributorID, CustomerTypeID, DescriptionOfficial, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, FirstChangeLog, CustomerChannelID) VALUES (@SerialID, @ImportBatchID, @CustomerID, @DistributorID, @CustomerTypeID, @DescriptionOfficial, @VATCode, @ContactName, @AttentionName, @Telephone, @EMailAddress, @AddressNo, @AddressStreet, @AddressAreaID, @FirstChangeLog, @CustomerChannelID) " + "\r\n";
            queryString = queryString + "       SELECT      SerialID, ImportBatchID, CustomerID, DistributorID, CustomerTypeID, DescriptionOfficial, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, FirstChangeLog, CustomerChannelID FROM ListCustomerNameTracking WHERE SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameTrackingInsert", queryString);


            queryString = "     @SerialID int, @ImportBatchID int, @CustomerID int, @DistributorID int,	@CustomerTypeID int, @DescriptionOfficial nvarchar(100), @VATCode nvarchar(60), @ContactName nvarchar(100),	@AttentionName nvarchar(60), @Telephone nvarchar(60), @EMailAddress nvarchar(60), @AddressNo nvarchar(150), @AddressStreet nvarchar(150), @AddressAreaID int, @FirstChangeLog bit, @CustomerChannelID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListCustomerNameTracking SET ImportBatchID = @ImportBatchID, CustomerID = @CustomerID, DistributorID = @DistributorID, CustomerTypeID = @CustomerTypeID, DescriptionOfficial = @DescriptionOfficial, VATCode = @VATCode, ContactName = @ContactName, AttentionName = @AttentionName, Telephone = @Telephone, EMailAddress = @EMailAddress, AddressNo = @AddressNo, AddressStreet = @AddressStreet, AddressAreaID = @AddressAreaID, FirstChangeLog = @FirstChangeLog, CustomerChannelID = @CustomerChannelID WHERE SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      SerialID, ImportBatchID, CustomerID, DistributorID, CustomerTypeID, DescriptionOfficial, VATCode, ContactName, AttentionName, Telephone, EMailAddress, AddressNo, AddressStreet, AddressAreaID, FirstChangeLog, CustomerChannelID FROM ListCustomerNameTracking WHERE SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListCustomerNameTrackingUpdate", queryString);


            queryString = "     @SerialID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE      ListCustomerNameTracking WHERE SerialID = @SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListCustomerNameTrackingDelete", queryString);





            queryString = " @DistributorID Int, @SerialID Int, @FieldID Int, @ColumnValue nvarChar(200), @MultipleMap Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";//@MultipleMap = 0: MAPPING 1 TO 1; @MultipleMap = 1: MAPPING n TO 1;
            queryString = queryString + "   IF @MultipleMap = 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";//NEU MUON MAPPING FROM MULTIPLE AVAILABLE ROWS INTO ONE REQUIRED ROW => MUST KEEP THIS BELOW STATEMENT. TO USE THIS FOLLOW SATTEMENT, WE AGREE THAT: THERE IS A SINGLE ROW FROM AVAILABLE MAP INTO REQUIRED ROW
            queryString = queryString + "           DELETE FROM " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = @DistributorID AND " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + " = @FieldID " + "\r\n";//DELETE CURRENT MAPPING OF THIS ROW
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   DELETE FROM " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = @DistributorID AND ColumnValue = @ColumnValue " + "\r\n"; //DELETE CURRENT MAPPING OF THIS ROW
            queryString = queryString + "   INSERT INTO " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " (DistributorID, SerialID, " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.EFieldID) + ", ColumnValue, ImportDate) VALUES (@DistributorID, @SerialID, @FieldID, @ColumnValue, GetDate()) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("Map" + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName), queryString);

            // UNMAP
            queryString = " @DistributorID Int, @ColumnValue nvarChar(200) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n"; //REMOVE MAPPING FROM THE LEFT ONLY (VSFAVAILABLE ONLY)
            queryString = queryString + "   DELETE FROM " + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName) + " WHERE DistributorID = @DistributorID AND ColumnValue = @ColumnValue " + "\r\n"; //DELETE CURRENT MAPPING OF THIS ROW ON VSFAVAILABLE ONLY

            SQLDatabase.CreateStoredProcedure("UnMap" + lfGetTableFieldSpName(this.lColumnID, ETableFieldSpName.ETableName), queryString);
        }




    }
}
